package com.vortex.cloud.zhsw.xinyang.util;

import cn.hutool.core.collection.CollUtil;
import com.alibaba.fastjson.JSON;
import com.github.liaochong.myexcel.core.SaxExcelReader;
import com.github.liaochong.myexcel.core.converter.ReadConverterContext;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.vortex.cloud.lbs.enums.CoordtypeEnum;
import com.vortex.cloud.lbs.enums.ShapeTypeEnum;
import com.vortex.cloud.zhsw.xinyang.converter.GeometryReadConverter;
import com.vortex.cloud.vfs.lite.base.dto.AbstractBaseDTO;
import com.vortex.cloud.vfs.lite.base.dto.FileDTO;
import com.vortex.cloud.vfs.lite.base.dto.GeometryDTO;
import com.vortex.cloud.vfs.lite.base.dto.excel.ExcelColumnDTO;
import com.vortex.cloud.vfs.lite.base.dto.excel.ExcelMessageDTO;
import com.vortex.cloud.vfs.lite.base.dto.excel.ExcelReadDTO;
import com.vortex.cloud.vfs.lite.export.dto.ExportLogDTO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.springframework.http.HttpHeaders;
import org.springframework.http.ResponseEntity;
import org.springframework.util.Assert;
import org.springframework.web.multipart.MultipartFile;

import javax.imageio.ImageIO;
import javax.imageio.ImageReader;
import javax.imageio.stream.ImageInputStream;
import java.awt.image.BufferedImage;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URL;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.nio.file.CopyOption;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.StandardCopyOption;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * Excel工具类
 *
 * @author huohao@vortexinfo.cn
 * @date 2021/9/17 9:06
 */
@Slf4j
public class ExcelUtils {

    /**
     * 读取Excel数据
     *
     * @param file                 Excel文件
     * @param clazz                读取的类型DTO
     * @param startRowNum          开始行
     * @param validateAndTransform 验证和信息（主要验证必填、格式、字典名称是否存在对应值、验重复，对于字典值将名称反写到对应字段）
     * @return com.vortex.cloud.vfs.lite.base.dto.excel.ExcelReadDTO<DTO>
     * @author huohao@vortexinfo.cn
     */
    public static <DTO extends AbstractBaseDTO<DTO>> ExcelReadDTO<DTO> readExcel(MultipartFile file, Class<DTO> clazz, Integer startRowNum, ExcelImportValidate<DTO> validateAndTransform, CoordtypeEnum coordtypeEnum, ShapeTypeEnum shapeTypeEnum) {
        try {
            Assert.notNull(file, "文件不能为空");
            List<ExcelMessageDTO> messages = Lists.newArrayList();
            List<DTO> dataList = Lists.newArrayList();
            InputStream inputStream = file.getInputStream();
            return getDataList(clazz, startRowNum, validateAndTransform, messages, dataList, inputStream, coordtypeEnum, shapeTypeEnum);
        } catch (IOException e) {
            log.error("EXCEL读取异常", e);
            throw new RuntimeException(e);
        } catch (IntrospectionException e) {
            log.error("EXCEL读取异常", e);
        }
        return null;
    }


    public static <DTO extends AbstractBaseDTO<DTO>> ExcelReadDTO<DTO> readExcel(MultipartFile file, Class<DTO> clazz, Integer sheetIndex, Integer startRowNum, ExcelImportValidate<DTO> validateAndTransform, CoordtypeEnum coordtypeEnum, ShapeTypeEnum shapeTypeEnum) {
        try {
            Assert.notNull(file, "文件不能为空");
            List<ExcelMessageDTO> messages = Lists.newArrayList();
            List<DTO> dataList = Lists.newArrayList();
            InputStream inputStream = file.getInputStream();
            return getDataList(clazz, startRowNum, validateAndTransform, messages, dataList, inputStream, coordtypeEnum, shapeTypeEnum, sheetIndex);
        } catch (IOException e) {
            log.error("EXCEL读取异常", e);
            throw new RuntimeException(e);
        } catch (IntrospectionException e) {
            log.error("EXCEL读取异常", e);
        }
        return null;
    }

    /**
     * 读取Excel数据
     *
     * @param file                 Excel文件
     * @param clazz                读取的类型DTO
     * @param startRowNum          开始行
     * @param validateAndTransform 验证和信息（主要验证必填、格式、字典名称是否存在对应值、验重复，对于字典值将名称反写到对应字段）
     * @return com.vortex.cloud.vfs.lite.base.dto.excel.ExcelReadDTO<DTO>
     * @author huohao@vortexinfo.cn
     */
    public static <DTO extends AbstractBaseDTO<DTO>> ExcelReadDTO<DTO> readExcel(File file, Class<DTO> clazz, Integer startRowNum, ExcelImportValidate<DTO> validateAndTransform, CoordtypeEnum coordtypeEnum, ShapeTypeEnum shapeTypeEnum) {
        try {
            Assert.notNull(file, "文件不能为空");
            List<ExcelMessageDTO> messages = Lists.newArrayList();
            List<DTO> dataList = Lists.newArrayList();
            InputStream inputStream = new FileInputStream(file);
            return getDataList(clazz, startRowNum, validateAndTransform, messages, dataList, inputStream, coordtypeEnum, shapeTypeEnum);
        } catch (IOException e) {
            log.error("EXCEL读取异常", e);
            throw new RuntimeException(e);
        } catch (IntrospectionException e) {
            log.error("EXCEL读取异常", e);
        }
        return null;
    }

    private static <DTO extends AbstractBaseDTO<DTO>> ExcelReadDTO<DTO> getDataList(Class<DTO> clazz, Integer startRowNum, ExcelImportValidate<DTO> validateAndTransform, List<ExcelMessageDTO> messages, List<DTO> dataList, InputStream inputStream, CoordtypeEnum coordtypeEnum, ShapeTypeEnum shapeTypeEnum) throws IOException, IntrospectionException {
        new ReadConverterContext().registering(GeometryDTO.class, new GeometryReadConverter());
        // 经纬度字段名称
        List<String> geometryFieldList = org.apache.commons.compress.utils.Lists.newArrayList();
        // 经纬度字段get方法
        Map<String, Method> getMethodMap = Maps.newHashMap();
        // 经纬度字段set方法
        Map<String, Method> setMethodMap = Maps.newHashMap();
        for (Field field : clazz.getDeclaredFields()) {
            if (field.getType().equals(GeometryDTO.class)) {
                geometryFieldList.add(field.getName());
                PropertyDescriptor pd = new PropertyDescriptor(field.getName(), clazz);
                getMethodMap.put(field.getName(), pd.getReadMethod());
                setMethodMap.put(field.getName(), pd.getWriteMethod());
            }
        }
        SaxExcelReader.of(clazz).exceptionally((e, context) -> {
            ExcelMessageDTO messageDTO = new ExcelMessageDTO();
            messageDTO.setMessages(Collections.singletonList(context.getField().getName()));
            messageDTO.setLineNum(context.getRowNum() + 1);
            messages.add(messageDTO);
            return true;
        }).sheet(0).rowFilter(row -> row.getRowNum() >= startRowNum).readThen(inputStream, (record, rowContext) -> {
            // 设置经纬度字段 shapeType 和CoordinateType
            geometryFieldList.forEach(geometryField -> {
                try {
                    GeometryDTO geo = (GeometryDTO) getMethodMap.get(geometryField).invoke(record);
                    geo.setShapeType(Objects.nonNull(shapeTypeEnum) ? shapeTypeEnum.getKey() : ShapeTypeEnum.POLYGON.getKey());
                    geo.setCoordinateType(Objects.nonNull(coordtypeEnum) ? coordtypeEnum.getKey() : CoordtypeEnum.gps.getKey());
                    setMethodMap.get(geometryField).invoke(record, geo);
                } catch (IllegalAccessException | InvocationTargetException e) {
                    log.error("EXCEL读取异常", e);
                }
            });
            if (Objects.nonNull(validateAndTransform)) {
                // 个性化校验
                List<String> msg = validateAndTransform.validate(record);
                if (CollectionUtils.isNotEmpty(msg)) {
                    ExcelMessageDTO messageDTO = new ExcelMessageDTO();
                    messageDTO.setMessages(msg);
                    messageDTO.setLineNum(rowContext.getRowNum() + 1);
                    messages.add(messageDTO);
                } else {
                    dataList.add(record);
                }
            } else {
                dataList.add(record);
            }
        });
        if (inputStream != null) {
            inputStream.close();
        }
        return new ExcelReadDTO<>(dataList, messages);
    }

    public static <T> void exportToFileStepByStep(String fileName, int pageSize, String extension, String columnJson, ExportLogDTO export, IFetchExportData<T> fetcher) {

        int pageNum = 0;
        Workbook workbook = new SXSSFWorkbook();
        try {
            while (true) {
                pageNum++;
                List<T> data = fetcher.fetchData(pageNum, pageSize);
                if (CollUtil.isEmpty(data)) {
                    break;
                }
                List<ExcelColumnDTO> columns = JSON.parseArray(columnJson, ExcelColumnDTO.class);
                generateSheet(null,workbook, null, columns, data,null);
            }
            byte[] bytesExtend = exportExcelExtend(workbook, fileName, extension).getBody();
            FileOutputStream fosExtend = new FileOutputStream(export.getEmptyFile().getAbsoluteFile());
            fosExtend.write(bytesExtend);
            fosExtend.close();

        } catch (Exception e) {
            throw new RuntimeException(e);
        }


    }

    public interface IFetchExportData<T> {
        List<T> fetchData(int pageNum, int pageSize);
    }


    private static <T> Workbook generateWorkbook(String extension, String downloadUrl, String columnJson, List<T> list) {
        try {
            List<ExcelColumnDTO> columns = JSON.parseArray(columnJson, ExcelColumnDTO.class);
            Assert.notEmpty(columns, "数据列不能为空");
            Iterator var5 = columns.iterator();

            while (var5.hasNext()) {
                ExcelColumnDTO column = (ExcelColumnDTO) var5.next();
                Assert.hasText(column.getTitle(), "数据列标题不能为空");
                Assert.hasText(column.getField(), "数据列字段不能为空");
            }

            if (!StringUtils.equalsIgnoreCase(extension, "xls") && !StringUtils.equalsIgnoreCase(extension, "xlsx")) {
                extension = "xls";
            }

            Workbook workbook = StringUtils.equalsIgnoreCase(extension, "xls") ? new HSSFWorkbook() : new XSSFWorkbook();
            generateSheet(null,(Workbook) workbook, downloadUrl, columns, list,null);
            return (Workbook) workbook;
        } catch (Exception var7) {
            throw new RuntimeException(var7);
        }
    }

    public static <T> void exportBigData(List<T> list, Integer size, String fileName, String extension, String columnJson, ExportLogDTO export) {
        if (Objects.isNull(size)) {
            size = 10000;
        }
        int current = 0;
        int pageSize = size;
        int startIndex = 0;
        int endIndex = Math.min(pageSize, list.size());
        try {
//            List<T> pageOne = list.subList(startIndex, endIndex);
//           byte[] bytes = com.vortex.cloud.vfs.lite.base.util.ExcelUtils.exportExcel(fileName, extension, null, columnJson, pageOne).getBody();
//            FileOutputStream fos = new FileOutputStream(export.getEmptyFile().getAbsoluteFile());
//            fos.write(bytes);
//            fos.close();
            boolean k = true;
//            current++;
//            startIndex = current * pageSize;
//            endIndex = Math.min(startIndex + pageSize, list.size());
//
//            FileInputStream in = new FileInputStream(export.getEmptyFile().getAbsoluteFile());
            Workbook workbook = new SXSSFWorkbook(pageSize);
            Sheet sheet = workbook.createSheet();
            while (k) {
                if (endIndex == list.size()) {
                    k = false;
                }
                List<T> page = list.subList(startIndex, endIndex);
                List<ExcelColumnDTO> columns = JSON.parseArray(columnJson, ExcelColumnDTO.class);
                //处理业务
                generateSheet(sheet, workbook,null, columns, page,startIndex);
                current++;
                startIndex = current * pageSize;
                endIndex = Math.min(startIndex + pageSize, list.size());
            }

            byte[] bytesExtend = exportExcelExtend(workbook, fileName, extension).getBody();
            FileOutputStream fosExtend = new FileOutputStream(export.getEmptyFile().getAbsoluteFile());
            fosExtend.write(bytesExtend);
            fosExtend.close();
        } catch (Exception e) {
            log.error(e.getMessage());
        }

    }


    public static <T> ResponseEntity<byte[]> exportExcelExtend(Workbook workbook, String fileName, String extension) {

        try {
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            Throwable var7 = null;

            ResponseEntity var10;
            try {
                String fullName = URLEncoder.encode(fileName + "." + extension, StandardCharsets.UTF_8.name());
                HttpHeaders httpHeaders = new HttpHeaders();
                httpHeaders.add("Content-Type", "application/octet-stream");
                httpHeaders.add("Accept-Charset", StandardCharsets.UTF_8.name());
                httpHeaders.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + fullName);
                workbook.write(byteArrayOutputStream);
                workbook.close();
                byteArrayOutputStream.flush();
                byteArrayOutputStream.close();
                var10 = ((ResponseEntity.BodyBuilder) ResponseEntity.ok().headers(httpHeaders)).body(byteArrayOutputStream.toByteArray());
            } catch (Throwable var20) {
                var7 = var20;
                throw var20;
            } finally {
                if (byteArrayOutputStream != null) {
                    if (var7 != null) {
                        try {
                            byteArrayOutputStream.close();
                        } catch (Throwable var19) {
                            var7.addSuppressed(var19);
                        }
                    } else {
                        byteArrayOutputStream.close();
                    }
                }

            }

            return var10;
        } catch (Exception var22) {
            log.error("导出失败", var22);
            throw new RuntimeException(var22);
        }
    }


    public static <T> Sheet generateSheet(Sheet sheet,Workbook workbook, String downloadUrl, List<ExcelColumnDTO> columns, List<T> list,Integer num) throws Exception {
        CreationHelper creationHelper = workbook.getCreationHelper();
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        CellStyle hyperLinkCellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setUnderline((byte) 1);
        font.setColor(IndexedColors.BLUE.getIndex());
        hyperLinkCellStyle.setFont(font);
        hyperLinkCellStyle.setAlignment(HorizontalAlignment.CENTER);
        hyperLinkCellStyle.setBorderBottom(BorderStyle.THIN);
        hyperLinkCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        hyperLinkCellStyle.setBorderLeft(BorderStyle.THIN);
        hyperLinkCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        hyperLinkCellStyle.setBorderRight(BorderStyle.THIN);
        hyperLinkCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        hyperLinkCellStyle.setBorderTop(BorderStyle.THIN);
        hyperLinkCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

        int t = num;
        if(num==0){
            Row titleRow = sheet.createRow(0);
            Cell seqNoTitleCell = titleRow.createCell(0);
            seqNoTitleCell.setCellValue("序号");
            seqNoTitleCell.setCellStyle(cellStyle);
            for(int i = 0; i < columns.size(); ++i) {
                ExcelColumnDTO excelColumnDTO = columns.get(i);
                int column = i + 1;
                Cell cell = titleRow.createCell(column);
                cell.setCellValue(excelColumnDTO.getTitle());
                cell.setCellStyle(cellStyle);
            }
        }
        for (int i = t; i < list.size() + t; i++) {
            Row contentRow = sheet.createRow(i + 1);
            Cell seqNoCell = contentRow.createCell(0);
            seqNoCell.setCellValue(i + 1);
            seqNoCell.setCellStyle(cellStyle);
            T item = list.get(i - t);

            for (int j = 0; j < columns.size(); ++j) {
                Cell cell = contentRow.createCell(j + 1);
                cell.setCellStyle(cellStyle);
                ExcelColumnDTO column = columns.get(j);
                String fieldName = column.getField();
                String fieldType = column.getType();
                String fieldFormat = column.getFormat();
                Object objectValue = null;
                String[] attributes = fieldName.split("\\.");
                if (attributes.length == 1) {
                    objectValue = getFieldValueByName(fieldName, fieldFormat, item);
                } else {
                    try {
                        objectValue = getFieldValueByNameSequence(fieldName, fieldFormat, item);
                    } catch (Exception var26) {
                        log.warn("无法通过递归找到子属性{}", fieldName);
                    }
                }

                if (!Objects.isNull(objectValue)) {
                    setCellValue(workbook, creationHelper, drawing, cell, hyperLinkCellStyle, downloadUrl, fieldType, objectValue);
                }
            }
        }

//        for (int i = 0; i <= columns.size(); ++i) {
//            sheet.autoSizeColumn(i);
//            int columnWidth = sheet.getColumnWidth(i) * 17 / 10;
//            if (columnWidth > 65280) {
//                columnWidth = 65280;
//            }
//
//            sheet.setColumnWidth(i, columnWidth);
//        }
        if (sheet instanceof SXSSFSheet) {
            ((SXSSFSheet)sheet).flushRows();
        }
        return sheet;

    }


    private static void setCellValue(Workbook workbook, CreationHelper creationHelper, Drawing<?> drawing, Cell cell, CellStyle hyperlinkCellStyle, String downloadUrl, String fieldType, Object objectValue) throws Exception {
        if (!Objects.equals(fieldType, "file") && !Objects.equals(fieldType, "picture")) {
            if (objectValue instanceof Boolean) {
                if (BooleanUtils.toBoolean(Objects.toString(objectValue, (String) null))) {
                    cell.setCellValue("是");
                } else {
                    cell.setCellValue("否");
                }
            } else {
                cell.setCellValue(Objects.toString(objectValue, (String) null));
            }
        } else {
            int columnIndex = cell.getColumnIndex();
            int rowIndex = cell.getRowIndex();
            List<FileDTO> files = (List) objectValue;
            if (CollectionUtils.isNotEmpty(files)) {
                Assert.hasText(downloadUrl, "附件下载地址不能为空");
                if (Objects.equals(fieldType, "file")) {
                    FileDTO file = (FileDTO) files.get(0);
                    Hyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.URL);
                    hyperlink.setAddress(downloadUrl + file.getId());
                    hyperlink.setLabel(file.getName());
                    cell.setHyperlink(hyperlink);
                    cell.setCellValue(file.getName());
                    cell.setCellStyle(hyperlinkCellStyle);
                } else if (Objects.equals(fieldType, "picture")) {
                    for (int j = 0; j < (files.size() <= 5 ? files.size() : 5); ++j) {
                        FileDTO file = (FileDTO) files.get(j);
                        String fileUrl = downloadUrl + file.getId();
                        URL url = new URL(fileUrl);
                        Path tempPath = Files.createTempFile("image", (String) null);

                        try {
                            InputStream stream = url.openStream();
                            Throwable var17 = null;

                            try {
                                Files.copy(stream, tempPath, new CopyOption[]{StandardCopyOption.REPLACE_EXISTING});
                                File tempFile = tempPath.toFile();
                                ImageInputStream iis = ImageIO.createImageInputStream(tempFile);
                                Iterator<ImageReader> iter = ImageIO.getImageReaders(iis);
                                ImageReader reader = (ImageReader) iter.next();
                                String formatName = reader.getFormatName();
                                BufferedImage bi = ImageIO.read(tempFile);
                                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                                ImageIO.write(bi, formatName, baos);
                                int format = 5;
                                if (StringUtils.equalsIgnoreCase(formatName, "png")) {
                                    format = 6;
                                }

                                ClientAnchor clientAnchor = drawing.createAnchor(j * 200, 0, (j + 1) * 200, 255, columnIndex, rowIndex, columnIndex, rowIndex);
                                int pictureIndex = workbook.addPicture(baos.toByteArray(), format);
                                drawing.createPicture(clientAnchor, pictureIndex);
                                baos.close();
                                iis.close();
                                Files.delete(tempPath);
                            } catch (Throwable var36) {
                                var17 = var36;
                                throw var36;
                            } finally {
                                if (stream != null) {
                                    if (var17 != null) {
                                        try {
                                            stream.close();
                                        } catch (Throwable var35) {
                                            var17.addSuppressed(var35);
                                        }
                                    } else {
                                        stream.close();
                                    }
                                }

                            }
                        } catch (Exception var38) {
                            log.error("图片导出失败" + fileUrl, var38);
                        }
                    }
                }
            }
        }

    }

    private static Object getFieldValueByNameSequence(String fieldName, String fieldFormat, Object object) throws Exception {
        String[] attributes = fieldName.split("\\.");
        if (attributes.length == 1) {
            return getFieldValueByName(fieldName, fieldFormat, object);
        } else {
            Object fieldObj = getFieldValueByName(attributes[0], fieldFormat, object);
            String subFieldName = fieldName.substring(fieldName.indexOf(".") + 1);
            return getFieldValueByNameSequence(subFieldName, fieldFormat, fieldObj);
        }
    }

    private static Field getFieldByName(String fieldName, Class<?> clazz) {
        Field[] selfFields = clazz.getDeclaredFields();
        Field[] var3 = selfFields;
        int var4 = selfFields.length;

        for (int var5 = 0; var5 < var4; ++var5) {
            Field field = var3[var5];
            if (field.getName().equals(fieldName)) {
                return field;
            }
        }

        Class<?> superClazz = clazz.getSuperclass();
        if (superClazz != null && superClazz != Object.class) {
            return getFieldByName(fieldName, superClazz);
        } else {
            return null;
        }
    }

    private static Object getFieldValueByName(String fieldName, String fieldFormat, Object object) throws Exception {
        Object value;
        if (Map.class.isAssignableFrom(object.getClass())) {
            value = ((Map) object).get(fieldName);
        } else {
            Field field = getFieldByName(fieldName, object.getClass());
            if (field == null) {
                throw new Exception(object.getClass().getSimpleName() + "类不存在字段名" + fieldName);
            }

            field.setAccessible(true);
            value = field.get(object);
        }

        if (value instanceof Date) {
            if (StringUtils.isBlank(fieldFormat)) {
                fieldFormat = "yyyy-MM-dd HH:mm:ss";
            }

            value = (new DateTime(value)).toString(fieldFormat);
        } else if (value instanceof LocalDateTime) {
            if (StringUtils.isBlank(fieldFormat)) {
                fieldFormat = "yyyy-MM-dd HH:mm:ss";
            }

            value = DateTimeFormatter.ofPattern(fieldFormat).format((LocalDateTime) value);
        } else if (value instanceof LocalDate) {
            if (StringUtils.isBlank(fieldFormat)) {
                fieldFormat = "yyyy-MM-dd";
            }

            value = DateTimeFormatter.ofPattern(fieldFormat).format((LocalDate) value);
        } else if (value instanceof LocalTime) {
            if (StringUtils.isBlank(fieldFormat)) {
                fieldFormat = "HH:mm:ss";
            }

            value = DateTimeFormatter.ofPattern(fieldFormat).format((LocalTime) value);
        } else if (value instanceof DateTime) {
            if (StringUtils.isBlank(fieldFormat)) {
                fieldFormat = "yyyy-MM-dd HH:mm:ss";
            }

            value = ((DateTime) value).toString(fieldFormat);
        } else if (value instanceof org.joda.time.LocalDateTime) {
            if (StringUtils.isBlank(fieldFormat)) {
                fieldFormat = "yyyy-MM-dd HH:mm:ss";
            }

            value = ((org.joda.time.LocalDateTime) value).toString(fieldFormat);
        } else if (value instanceof org.joda.time.LocalDate) {
            if (StringUtils.isBlank(fieldFormat)) {
                fieldFormat = "yyyy-MM-dd";
            }

            value = ((org.joda.time.LocalDate) value).toString(fieldFormat);
        } else if (value instanceof org.joda.time.LocalTime) {
            if (StringUtils.isBlank(fieldFormat)) {
                fieldFormat = "HH:mm:ss";
            }

            value = ((org.joda.time.LocalTime) value).toString(fieldFormat);
        }

        return value;
    }


    private static <DTO extends AbstractBaseDTO<DTO>> ExcelReadDTO<DTO> getDataList(
            Class<DTO> clazz, Integer startRowNum, ExcelImportValidate<DTO> validateAndTransform,
            List<ExcelMessageDTO> messages, List<DTO> dataList, InputStream inputStream,
            CoordtypeEnum coordtypeEnum, ShapeTypeEnum shapeTypeEnum, Integer sheetIndex) throws IOException, IntrospectionException {
        new ReadConverterContext().registering(GeometryDTO.class, new GeometryReadConverter());
        // 经纬度字段名称
        List<String> geometryFieldList = org.apache.commons.compress.utils.Lists.newArrayList();
        // 经纬度字段get方法
        Map<String, Method> getMethodMap = Maps.newHashMap();
        // 经纬度字段set方法
        Map<String, Method> setMethodMap = Maps.newHashMap();
        for (Field field : clazz.getDeclaredFields()) {
            if (field.getType().equals(GeometryDTO.class)) {
                geometryFieldList.add(field.getName());
                PropertyDescriptor pd = new PropertyDescriptor(field.getName(), clazz);
                getMethodMap.put(field.getName(), pd.getReadMethod());
                setMethodMap.put(field.getName(), pd.getWriteMethod());
            }
        }
        SaxExcelReader.of(clazz).exceptionally((e, context) -> {
            ExcelMessageDTO messageDTO = new ExcelMessageDTO();
            messageDTO.setMessages(Collections.singletonList(context.getField().getName()));
            messageDTO.setLineNum(context.getRowNum() + 1);
            messages.add(messageDTO);
            return true;
        }).sheet(sheetIndex).rowFilter(row -> row.getRowNum() >= startRowNum).readThen(inputStream, (record, rowContext) -> {
            // 设置经纬度字段 shapeType 和CoordinateType
            geometryFieldList.forEach(geometryField -> {
                try {
                    GeometryDTO geo = (GeometryDTO) getMethodMap.get(geometryField).invoke(record);
                    geo.setShapeType(Objects.nonNull(shapeTypeEnum) ? shapeTypeEnum.getKey() : ShapeTypeEnum.POLYGON.getKey());
                    geo.setCoordinateType(Objects.nonNull(coordtypeEnum) ? coordtypeEnum.getKey() : CoordtypeEnum.gps.getKey());
                    setMethodMap.get(geometryField).invoke(record, geo);
                } catch (IllegalAccessException | InvocationTargetException e) {
                    log.error("EXCEL读取异常", e);
                }
            });
            if (Objects.nonNull(validateAndTransform)) {
                // 个性化校验
                List<String> msg = validateAndTransform.validate(record);
                if (CollectionUtils.isNotEmpty(msg)) {
                    ExcelMessageDTO messageDTO = new ExcelMessageDTO();
                    messageDTO.setMessages(msg);
                    messageDTO.setLineNum(rowContext.getRowNum() + 1);
                    messages.add(messageDTO);
                } else {
                    dataList.add(record);
                }
            } else {
                dataList.add(record);
            }
        });
        if (inputStream != null) {
            inputStream.close();
        }
        return new ExcelReadDTO<>(dataList, messages);
    }


}
